﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Threading;
using System.Windows.Forms;
using OxyPlot;
using OxyPlot.Series;
using OxyPlot.Axes;

namespace GraphPlotting
{
    static class Program
    {
        private const int NROWS = 37;
        private const int NCOLS = 8;
        private static double[,] dataArray;

        #region PARTE 1 DO TRABALHO

        //This method will get the data from the excel sheet to a matrix of doubles
        public static void ReadFromExcel(Excel.Application xlApp, string s, ref double[,] dataArray)
        {
            //Here we declare the Excel range, workbook and worksheet to work with
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            //dataNum is the number of the cell we are extracting the data currently, rCnt and cCnt is the counter for the rows and columns
            double dataNum;
            int rCnt = 0;
            int cCnt = 0;

            //Here we are opening the Excel file
            xlWorkBook = xlApp.Workbooks.Open(s, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            //And here we are selecting the sheet number 1 from that file
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //This will get all the used cells from the sheet
            range = xlWorkSheet.UsedRange;

            //Now this for cicle will go throug all those cells and get the value of them
            //It starts at row 2 and column 3 due to the Excel file provided to work with. The actual data to be extracted only starts in cell (2, 3)
            for (rCnt = 2; rCnt <= NROWS + 1; rCnt++)
            {
                for (cCnt = 3; cCnt <= NCOLS + 2; cCnt++)
                {
                    if ((range.Cells[rCnt, cCnt] as Excel.Range).Value == null)
                    {
                        //If there is no value in the cell, place a 0 in the matrix
                        dataNum = 0;
                    }
                    else
                    {
                        //Or place the actual value, if it's not 0
                        dataNum = (double)(range.Cells[rCnt, cCnt] as Excel.Range).Value;
                    }
                    dataArray[rCnt - 2, cCnt - 3] = dataNum;
                }
            }

            //This will close the workbook, so there is no errors
            xlWorkBook.Close(true, null, null);

        }

        //This method will place the collected data to a database file
        public static void CreateTable(double[,] dataArray)
        {
            // These strings will create the connection to a database file:
            string sqliteDBFile = @"Database.db";
            string connStr = @"data source=" + sqliteDBFile;

            // This is the connection code for the database to be open:
            SQLiteConnection cnn = new SQLiteConnection(connStr);
            cnn.Open();

            try
            {
                //This is the command to create the table Data, which will recieve eight inputs (8 columns)
                using (SQLiteCommand command = new SQLiteCommand("CREATE TABLE Data(IPCGlobal DOUBLE, VariacaoAnual DOUBLE, RenumeracaoMinimaMensal DOUBLE, RenumeracaoMaximaMensal DOUBLE, PIBperCapitaAnual DOUBLE, RendimentoNacionalBrutoPerCapitaAnual DOUBLE, RendimentoDisponivelBrutoPerCapitaAnual DOUBLE, RemuneracoesPerCapitaAnual DOUBLE)", cnn))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch
            {
                //If the creation fails, this is the error to be printed to the console
                Console.WriteLine("A tabela não foi criada! (É possível que a tabela já exista)");
            }

            //This for cicle will place, row by row, the data in the respective column
            for (int i = 1; i <= NROWS; i++)
            {
                try
                {
                    //This is the command to insert the data
                    using (SQLiteCommand command = new SQLiteCommand("INSERT INTO Data VALUES(@IPCGlobal, @VariacaoAnual, @RenumeracaoMinimaMensal, @RenumeracaoMaximaMensal, @PIBperCapitaAnual, @RendimentoNacionalBrutoPerCapitaAnual, @RendimentoDisponivelBrutoPerCapitaAnual, @RemuneracoesPerCapitaAnual)", cnn))
                    {
                        //These are the parameters of that command. If the value reads 0, it places a null in the database, because it means it's a null in the excel sheet
                        command.Parameters.Add(new SQLiteParameter("IPCGlobal", (dataArray[i - 1, 0] == 0 ? (double?)null : dataArray[i - 1, 0])));
                        command.Parameters.Add(new SQLiteParameter("VariacaoAnual", (dataArray[i - 1, 1] == 0 ? (double?)null : dataArray[i - 1, 1])));
                        command.Parameters.Add(new SQLiteParameter("RenumeracaoMinimaMensal", (dataArray[i - 1, 2] == 0 ? (double?)null : dataArray[i - 1, 2])));
                        command.Parameters.Add(new SQLiteParameter("RenumeracaoMaximaMensal", (dataArray[i - 1, 3] == 0 ? (double?)null : dataArray[i - 1, 3])));
                        command.Parameters.Add(new SQLiteParameter("PIBperCapitaAnual", (dataArray[i - 1, 4] == 0 ? (double?)null : dataArray[i - 1, 4])));
                        command.Parameters.Add(new SQLiteParameter("RendimentoNacionalBrutoPerCapitaAnual", (dataArray[i - 1, 5] == 0 ? (double?)null : dataArray[i - 1, 5])));
                        command.Parameters.Add(new SQLiteParameter("RendimentoDisponivelBrutoPerCapitaAnual", (dataArray[i - 1, 6] == 0 ? (double?)null : dataArray[i - 1, 6])));
                        command.Parameters.Add(new SQLiteParameter("RemuneracoesPerCapitaAnual", (dataArray[i - 1, 7] == 0 ? (double?)null : dataArray[i - 1, 7])));

                        command.ExecuteNonQuery();
                    }
                }
                catch
                {
                    //If placing the data fails, this is the messasge error to be printed
                    Console.WriteLine("Não foi possível inserir os dados.");
                }
            }
        }

        #endregion

        #region PARTE 2 DO TRABALHO

        public static PlotModel addLine()
        {
            var myModel = new PlotModel { Title = "Gráfico" };

            if (Form1.getGraphNum() == 1)
            {
                var lineSeries = new LineSeries();
                lineSeries.Color = OxyColors.Green;
                lineSeries.MarkerFill = OxyColors.Transparent;
                lineSeries.DataFieldX = "Ano";
                lineSeries.DataFieldY = "Valor";
                lineSeries.Title = Form1.getSelectedOne();

                for (int i = Form1.getYearStart(); i < Form1.getYearEnd(); i++)
                {
                    lineSeries.Points.Add(new DataPoint(i, dataArray[(i - 1977), Form1.getSelectedOneIndex()]));
                }

                myModel.Series.Add(lineSeries);
            }
            else
            {
                var lineSeries1 = new LineSeries();
                lineSeries1.Color = OxyColors.Blue;
                lineSeries1.MarkerFill = OxyColors.Transparent;
                lineSeries1.DataFieldX = "Ano";
                lineSeries1.DataFieldY = "Valor";
                lineSeries1.Title = Form1.getSelectedOne();

                for (int i = Form1.getYearStart(); i < Form1.getYearEnd(); i++)
                {
                    lineSeries1.Points.Add(new DataPoint(i, dataArray[(i - 1977), Form1.getSelectedOneIndex()]));
                }

                myModel.Series.Add(lineSeries1);

                var lineSeries2 = new LineSeries();
                lineSeries2.Color = OxyColors.Red;
                lineSeries2.MarkerFill = OxyColors.Transparent;
                lineSeries2.DataFieldX = "Ano";
                lineSeries2.DataFieldY = "Valor";
                lineSeries2.Title = Form1.getSelectedTwo();

                for (int j = Form1.getYearStart(); j < Form1.getYearEnd(); j++)
                {
                    lineSeries2.Points.Add(new DataPoint(j, dataArray[(j - 1977), Form1.getSelectedTwoIndex()]));
                }

                
                myModel.Series.Add(lineSeries2);
            }
            
            return myModel;
        }

        #endregion

        [STAThread]
        static void Main()
        {
            //These lines ill create Excel application, which will be used to work with .xls files
            Excel.Application xlApp = new Excel.Application();

            //And the double matrix to place the data from the excel sheet
            dataArray = new double[NROWS, NCOLS];

            string dam = Application.StartupPath + @"\\IPC_Portugal_1977_2013.xls";

            //The methods are now called
            ReadFromExcel(xlApp, dam, ref dataArray);
            CreateTable(dataArray);

            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}
